﻿Public Class frmInThongKeDoanhThu
    'ReportType 1- ngày; 2- tháng; 3-năm; 4-từ ngày đến ngày
    Public ReportType As Integer
    Public Ngay As String
    Public Thang As String
    Public Nam As String

    Public TuNgay As String
    Public DenNgay As String
    Public sTuNgay As String
    Public sDenNgay As String

    Private Sub frmInThongKeDoanhThu_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.WindowState = FormWindowState.Maximized
        Select Case ReportType
            Case 0
                Report_TheoNgay()
            Case 1
                Report_TheoThang()
            Case 2
                Report_TheoNam()
            Case 3
                Report_TuNgayDenNgay()
        End Select
    End Sub
    Protected Function Create_GroupingList() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try
            Dim KhachHangId As DataColumn = New DataColumn("KhachHangId")
            KhachHangId.DataType = System.Type.GetType("System.String")
            KhachHangId.DefaultValue = ""
            dtTable.Columns.Add(KhachHangId)

            Dim TenKhachHang As DataColumn = New DataColumn("TenKhachHang")
            TenKhachHang.DataType = System.Type.GetType("System.String")
            TenKhachHang.DefaultValue = ""
            dtTable.Columns.Add(TenKhachHang)

            Dim MaSoThue As DataColumn = New DataColumn("MaSoThue")
            MaSoThue.DataType = System.Type.GetType("System.String")
            MaSoThue.DefaultValue = ""
            dtTable.Columns.Add(MaSoThue)

            Dim DiaChi As DataColumn = New DataColumn("DiaChi")
            DiaChi.DataType = System.Type.GetType("System.String")
            DiaChi.DefaultValue = ""
            dtTable.Columns.Add(DiaChi)

            Dim NgayBan As DataColumn = New DataColumn("NgayBan")
            NgayBan.DataType = System.Type.GetType("System.String")
            dtTable.Columns.Add(NgayBan)

            Dim DoanhThu As DataColumn = New DataColumn("DoanhThu")
            DoanhThu.DataType = System.Type.GetType("System.String")
            DoanhThu.DefaultValue = ""
            dtTable.Columns.Add(DoanhThu)
            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
#Region "Thống kê - báo cáo"
    '--------Tính tiền doanh thu của khách hàng trên 1 đơn hàng-------------------
    Private Function TinhDoanhThuKhachHangTheoDonHang(ByVal IdDonHangBan As Integer) As Single
        Dim sRet As Single = 0
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtThanhTien As New DataTable
            Dim query_Tien_HoaDon As String
            Dim adapter As OleDb.OleDbDataAdapter
            Dim TongTienHoaDon As Integer = 0
            query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where LoaiPhi='HangHoa' And IdDonHangBan={0} ", IdDonHangBan)
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
            Next
            Return sRet
        Catch ex As Exception
            MessageBox.Show("Có lỗi trong quá trình xử lý.Vui lòng thử lại")
        End Try
    End Function
    Private Function TinhTongDoanhThuKhachHang(ByVal KhachHangId As Integer, ByVal CalOption As Integer)
        Dim sRet As Single = 0
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtThanhTien As New DataTable
            Dim query_Tien_HoaDon As String
            Dim adapter As OleDb.OleDbDataAdapter
            Dim TongTienHoaDon As Integer = 0
            query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Đã duyệt' And LoaiPhi='HangHoa' And KhachHangId={0} ", KhachHangId)
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
            adapter.Fill(dtThanhTien)
            For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
            Next
            Return sRet
        Catch ex As Exception
            Throw
        End Try
    End Function
#End Region
    Sub Report_TheoNgay()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim query As String = ""
            REM Grouping list
            Dim groupingList As New DataTable("Data")
            groupingList = Create_GroupingList()
            'title
            Dim dtTenKhachHang As New DataTable
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where TrangThai=N'Đã duyệt' And NgayBan='" & Ngay & "'"
            query = query & " Union Select Distinct NhanVienTiepThi,NhanVienTiepThiId From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Đã duyệt' And NgayBan='" & Ngay & "'"
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtTenKhachHang)
            For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
                'detail
                query = "Select * From DonHangBan Where TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And NgayBan='" & Ngay & "' Order By Ngay_So DESC"
                Dim dtThongKe As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKe)
                dtThongKe.Columns.Add("DoanhThu")
                Dim sThanhTien As String
                Dim DoanhThu As Single = 0
                For j As Integer = 0 To dtThongKe.Rows.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(j)("IdDonHangBan"))
                    sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                    dtThongKe.Rows(j)("DoanhThu") = sThanhTien
                    formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("KhachHangId") = dtThongKe.Rows(j)("KhachHangId")
                    detailRow.Item("TenKhachHang") = dtThongKe.Rows(j)("TenKhachHang")
                    detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                    detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                    detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                    detailRow.Item("DoanhThu") = dtThongKe.Rows(j)("DoanhThu")
                    groupingList.Rows.Add(detailRow)
                Next
            Next
            dbConn.Close()
            Dim objReport As New rptDoanhThu
            objReport.Database.Tables("dtDoanhThu").SetDataSource(groupingList)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            formatDate2VN(Ngay)
            newRow("Title") = "Ngày " & Ngay
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception

        End Try
    End Sub
    Sub Report_TheoThang()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim query As String = ""
            REM Grouping list
            Dim groupingList As New DataTable("Data")
            groupingList = Create_GroupingList()
            'title
            Dim dtTenKhachHang As New DataTable
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where TrangThai=N'Đã duyệt' And Thang='" & Thang & "'"
            query = query & " Union Select Distinct NhanVienTiepThi,NhanVienTiepThiId From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Đã duyệt' And Thang='" & Thang & "'"
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtTenKhachHang)
            For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
                'detail
                query = "Select * From DonHangBan Where TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Thang='" & Thang & "' Order By Ngay_So DESC"
                Dim dtThongKe As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKe)
                dtThongKe.Columns.Add("DoanhThu")
                Dim sThanhTien As String
                Dim DoanhThu As Single = 0
                For j As Integer = 0 To dtThongKe.Rows.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(j)("IdDonHangBan"))
                    sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                    dtThongKe.Rows(j)("DoanhThu") = sThanhTien
                    formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("KhachHangId") = dtThongKe.Rows(j)("KhachHangId")
                    detailRow.Item("TenKhachHang") = dtThongKe.Rows(j)("TenKhachHang")
                    detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                    detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                    detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                    detailRow.Item("DoanhThu") = dtThongKe.Rows(j)("DoanhThu")
                    groupingList.Rows.Add(detailRow)
                Next
            Next
            dbConn.Close()
            Dim objReport As New rptDoanhThu
            objReport.Database.Tables("dtDoanhThu").SetDataSource(groupingList)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Tháng " & Thang
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception

        End Try
    End Sub
    Sub Report_TheoNam()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim query As String = ""
            REM Grouping list
            Dim groupingList As New DataTable("Data")
            groupingList = Create_GroupingList()
            'title
            Dim dtTenKhachHang As New DataTable
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where TrangThai=N'Đã duyệt' And Nam='" & Nam & "'"
            query = query & " Union Select Distinct NhanVienTiepThi,NhanVienTiepThiId From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Đã duyệt' And Nam='" & Nam & "'"
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtTenKhachHang)
            For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
                'detail
                query = "Select * From DonHangBan Where TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Nam='" & Nam & "' Order By Ngay_So DESC"
                Dim dtThongKe As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKe)
                dtThongKe.Columns.Add("DoanhThu")
                Dim sThanhTien As String
                Dim DoanhThu As Single = 0
                For j As Integer = 0 To dtThongKe.Rows.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(j)("IdDonHangBan"))
                    sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                    dtThongKe.Rows(j)("DoanhThu") = sThanhTien
                    formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("KhachHangId") = dtThongKe.Rows(j)("KhachHangId")
                    detailRow.Item("TenKhachHang") = dtThongKe.Rows(j)("TenKhachHang")
                    detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                    detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                    detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                    detailRow.Item("DoanhThu") = dtThongKe.Rows(j)("DoanhThu")
                    groupingList.Rows.Add(detailRow)
                Next
            Next
            dbConn.Close()
            Dim objReport As New rptDoanhThu
            objReport.Database.Tables("dtDoanhThu").SetDataSource(groupingList)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            newRow("Title") = "Năm " & Nam
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception

        End Try
    End Sub
    Sub Report_TuNgayDenNgay()
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim query As String = ""
            REM Grouping list
            Dim groupingList As New DataTable("Data")
            groupingList = Create_GroupingList()
            'title
            Dim dtTenKhachHang As New DataTable
            query = "Select Distinct TenKhachHang,KhachHangId From DonHangBan Where TrangThai=N'Đã duyệt' And  Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtTenKhachHang)
            For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
                'detail
                query = "Select * From DonHangBan Where TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order By Ngay_So DESC"
                Dim dtThongKe As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtThongKe)
                dtThongKe.Columns.Add("DoanhThu")
                Dim sThanhTien As String
                Dim DoanhThu As Single = 0
                For j As Integer = 0 To dtThongKe.Rows.Count - 1
                    'Tính Doanh Thu Khách Hàng
                    DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(j)("IdDonHangBan"))
                    sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                    dtThongKe.Rows(j)("DoanhThu") = sThanhTien
                    formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
                    'detail row
                    Dim detailRow As DataRow
                    detailRow = groupingList.NewRow
                    detailRow.Item("KhachHangId") = dtThongKe.Rows(j)("KhachHangId")
                    detailRow.Item("TenKhachHang") = dtThongKe.Rows(j)("TenKhachHang")
                    detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
                    detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
                    detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
                    detailRow.Item("DoanhThu") = dtThongKe.Rows(j)("DoanhThu")
                    groupingList.Rows.Add(detailRow)
                Next
            Next
            dbConn.Close()
            Dim objReport As New rptDoanhThu
            objReport.Database.Tables("dtDoanhThu").SetDataSource(groupingList)
            'title report
            Dim dtTitle As New DataTable
            dtTitle.Columns.Add("Title")
            Dim newRow As DataRow
            newRow = dtTitle.NewRow
            formatDate2VN(sTuNgay)
            formatDate2VN(sDenNgay)
            newRow("Title") = "Từ ngày " & sTuNgay & " Đến ngày " & sDenNgay
            dtTitle.Rows.Add(newRow)
            dtTitle.AcceptChanges()
            objReport.Database.Tables("dtTitleReport").SetDataSource(dtTitle)
            cvrThongKe.ReportSource = objReport
        Catch ex As Exception

        End Try
    End Sub
End Class